package com.hulu.yec.sys.role.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.hulu.yec.common.Constant;
import com.hulu.yec.common.exception.DaoException;
import com.hulu.yec.common.exception.DaoRuntimeException;
import com.hulu.yec.common.util.DBUtil;
import com.hulu.yec.common.util.LogUtil;
import com.hulu.yec.sys.entity.MenuEntity;
import com.hulu.yec.sys.role.entity.RoleEntity;
import com.hulu.yec.sys.user.service.SysUserDao;
import com.hulu.yec.sys.yecclass.entity.ClassEntity;

/**
 * RoleDao
 * 
 * @author zm
 *
 */
public class RoleDao {

	/**
	 * 角色查询
	 * 
	 * @param role
	 * @return
	 * @throws DaoException 
	 */
	public List<RoleEntity> list(RoleEntity role){
		StringBuffer sb = new StringBuffer();
		List<Object> params = new ArrayList<>();
		sb.append("select r.id, ");
		sb.append("r.name, ");
		sb.append("u.name AS 'userName', ");
		sb.append("r.operate_time, ");
		sb.append("r.state, ");
		sb.append("r.memo ");
		sb.append(" from sys_role r, ");
		sb.append("sys_user u WHERE r.operate_user_id=u.id");
		if (role.getState() != 0) {
			sb.append(" and r.state=? ");
			params.add(role.getState());
		}
		sb.append(" ORDER BY r.id DESC");
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
	//	LogUtil.i(Constant.LOGGER, sb.toString());
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			DBUtil.setParameter(params, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			List<RoleEntity> roleList = new ArrayList<>();
			while (resultSet.next()) {
				RoleEntity roleEntity = new RoleEntity();
				roleEntity.setId(resultSet.getInt("id"));
			//	LogUtil.i(Constant.LOGGER, "id="+resultSet.getInt("id"));
				roleEntity.setName(resultSet.getString("name"));
			//	LogUtil.i(Constant.LOGGER, "name="+resultSet.getString("name"));
				roleEntity.setUserName(resultSet.getString("userName"));
			//	LogUtil.i(Constant.LOGGER, "name1="+resultSet.getString("userName"));
				roleEntity.setOperate_time(resultSet.getString("operate_time"));
			//	LogUtil.i(Constant.LOGGER, "operate_time="+resultSet.getString("operate_time"));
				roleEntity.setState(resultSet.getInt("state"));
			//	LogUtil.i(Constant.LOGGER, "state="+resultSet.getInt("state"));
				roleEntity.setMemo(resultSet.getString("memo"));
			//	LogUtil.i(Constant.LOGGER, "memo="+resultSet.getString("memo"));
				roleList.add(roleEntity);
			}
			return roleList;
		} catch (SQLException e) {
			throw new RuntimeException("查询异常！！");
			
		}finally{
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 获取传来id的状态
	 * @param id
	 * @return
	 */
	public int getState(int id) {
		String sql = "SELECT state FROM sys_role WHERE id ="+id;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt("state");
		} catch (SQLException e) {
			throw new DaoRuntimeException("查询异常！！");
		}finally{
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}
	/**
	 * 状态更新
	 * 
	 * @param id
	 * @param state
	 * @return   
	 * @throws DaoException
	 */
	public int updateState(int id, int reState) {
		String sql = "UPDATE sys_role SET state =" + reState + "  WHERE id=" + id;
		LogUtil.i(Constant.LOGGER, "sql="+sql);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			return preparedStatement.executeUpdate(sql);
		} catch (SQLException e) {
			throw new RuntimeException("查询异常！！");
		}finally{
			DBUtil.close(connection, preparedStatement);
		}
	}

	/**
	 * 角色新增
	 * 
	 * @param name
	 * @param memo
	 * @param userName
	 * @return	
	 * @throws DaoException 
	 */
	public int add(int id, String name, String memo) {
		String sql = "insert into sys_role values(null,'"+name+"', null, "+id+" ,now(),1,"+memo+")";
				Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			return preparedStatement.executeUpdate(sql);
		} catch (SQLException e) {
			throw new RuntimeException("查询异常！！");
		}finally{
			DBUtil.close(connection, preparedStatement);
		}
		
	}

	/**
	 * 角色信息修改前回显
	 * 
	 * @param id
	 * @return
	 * @throws DaoException
	 */
	public static List<RoleEntity> prelist(int id) {
		String sql = "SELECT r.name, r.state, r.memo, u.name as 'userName' FROM sys_role r, sys_user u WHERE r.operate_user_id=u.id and r.id="+id;
//		LogUtil.i(Constant.LOGGER, "sql="+sql);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			List<RoleEntity> roleList = new ArrayList<>();
			while (resultSet.next()) {
				RoleEntity roleEntity = new RoleEntity();
				roleEntity.setName(resultSet.getString("name"));
				roleEntity.setState(resultSet.getInt("state"));
				roleEntity.setMemo(resultSet.getString("memo"));
				roleEntity.setUserName(resultSet.getString("userName"));
				roleEntity.setId(id);
				roleList.add(roleEntity);
			 }
			return roleList;
		}catch (SQLException e) {
			throw new RuntimeException("查询异常！！");
		}finally{
			DBUtil.close(connection, preparedStatement);
		}
	
}
	/**
	 * 角色修改
	 * 
	 * @param id
	 * @param name
	 * @param state
	 * @param memo
	 * @return 	

	 * @throws DaoException 
	 */
	public int update(int id, String name, int state, String memo) {
		String sql = "update sys_role set name='"+name+"',state="+state+", memo='"+memo+"',operate_time=now() where id="+id;
		LogUtil.i(Constant.LOGGER, "sql="+sql);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			return preparedStatement.executeUpdate(sql);
		} catch (SQLException e) {
			throw new RuntimeException("查询异常！！");
		}finally{
			DBUtil.close(connection, preparedStatement);
		}

	}

	/**
	 * 角色名字唯一验证
	 * @param name
	 * @param request
	 * @param response
	 * @throws IOException 
	 */
	public void onlyRoleName(String name, HttpServletRequest request,
			HttpServletResponse response) throws IOException{
		String sql = "select id FROM sys_role WHERE name='" + name + "'";
		response.setCharacterEncoding(Constant.ENCODING_UTF8);
		if (name != "") {
			List<RoleEntity> roleList = new ArrayList<>();
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet resultSet = null;
			try {
				connection = DBUtil.getConnection();
				preparedStatement = connection.prepareStatement(sql);
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					RoleEntity roleEntity = new RoleEntity();
					roleEntity.setId(resultSet.getInt("id"));
					roleList.add(roleEntity);
				}
				if (roleList.size() != 0) {
					String print = "　　角色名已存在!";
					response.getWriter().print(print);

				} else if (roleList.size() == 0) {
					String print = "　通过";
					response.getWriter().print(print);
				}
			} catch (SQLException e) {
				throw new RuntimeException("失败~");
			} finally {
				// 4、资源关闭
				DBUtil.close(connection, preparedStatement, resultSet);
			}
		} else {
			String print = "　　角色名不能为空!";
			response.getWriter().print(print);
		}
	}

	/**
	 * 清空该角色的权限
	 * @param idStr
	 */
	public void deleteRightById(String idStr) {
		String sql = "DELETE FROM role_menu WHERE role_id=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, idStr);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(connection, preparedStatement);
		}
	}

	/**
	 * 通过id获取该用户的根菜单（权限）
	 * 
	 * @param id
	 * @return
	 */
	public List<MenuEntity> getMainMenu( int id){
			String sql="SELECT * from sys_menu WHERE id in(SELECT menu_id FROM role_menu WHERE role_id=(SELECT id from sys_role WHERE id = ?)) AND is_leaf=2";
			return  new SysUserDao().excuteQuery(MenuEntity.class, sql, id);
		}
	/**
	 * 通过id获取该用户的子菜单（权限）
	 * 
	 * @param id
	 * @return
	 */
	public List<MenuEntity> getChildMenu( int id){
			String sql="SELECT * from sys_menu WHERE id in(SELECT menu_id FROM role_menu WHERE role_id=(SELECT id from sys_role WHERE id = ?)) AND is_leaf=1";
			return  new SysUserDao().excuteQuery(MenuEntity.class, sql, id);
		}
	/**
	 * 获取所有的根菜单
	 * 
	 * @param id
	 * @return
	 */
	public List<MenuEntity> getAllMainMenu( ){
			String sql="SELECT * from sys_menu WHERE is_leaf=2";
			return  new SysUserDao().excuteQuery(MenuEntity.class, sql);
		}
	/**
	 * 获取所有的子菜单（权限）
	 * 
	 * @param id
	 * @return
	 */
	public List<MenuEntity> getAllChildMenu(){
			String sql="SELECT * from sys_menu WHERE is_leaf=1";
			return  new SysUserDao().excuteQuery(MenuEntity.class, sql);
		}
	
	
	/**
	 * 给该角色添加权限
	 * @param roleId
	 * @param menuArray
	 * @return
	 */
	public boolean addRightById(String roleId, String[] menuArray) {
		for (String rightId : menuArray) {
			addRight(roleId, rightId);
		}
		return true;
	}	
	
	/**
	 * 授权操作
	 * @param roleId
	 * @param rightId
	 * @return 
	 */
	private boolean addRight(String roleId,String rightId){
		String sql = "INSERT INTO role_menu(role_id,menu_id)VALUES(?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, roleId);
			preparedStatement.setString(2, rightId);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(connection, preparedStatement);
		}
		return true;
	}
}
